
/*
This program uses the Census firm data and the MNE datasets to produce aggregate statistics

It creates the output for Appendix Tables A1-A5



*/

 **Set directories
cd ""  /* PROJECT ROOT FOLDER */

global output "output"
global input "data"
global disc_code "code"

global code code


 

   
*1. Economy for context on our sample
*************************************************** 
 use "$input/firm_2007_withtypes.dta", clear 

*Simplified mne_status 
replace mne_status="DOM" if mne_status==""
replace mne_status="FOR" if mne_status=="FO2"  
replace mne_status="DOMIMP" if mne_status=="DOM" & importer==1
replace mne_status = "MNEFORMAN" if foreign_manuf ==1 & mne_status =="MNE"

 
 *Manuf or Not firms
 gen t2="MAN" if type=="MAN" | type=="MAN MPRO"
  replace t2="NON MAN" if t2==""
   
  *Ignore foreign manuf detail for FOR firms
    replace foreign_manuf=1 if mne_status=="FOR"  

    
*create a separate category for domestic importers and non-importers
replace mne_status = "DOM_IMP" if mne_status=="DOM" & importer==1

*Make prof firm indicator
gen prof_firms=1 if emp_prof>0 & emp_prof~=.

gen firms=1



*small checks
gen sh_man_emp=emp_man/emp
gen sh_man_sales=sales_man/sales
replace aff_emp=0 if aff_emp==.
gen sh_dom_man_emp=emp_man/(man_aff_emp+emp_man)


*CHECK JEP message
summ sh_dom_man if sh_dom_man<1, d 


collapse (sum) firms sales sales_man emp emp_man emp_prof emp_manage imp_value rp_imp_value importer rp_importer  exp_value rp_exp_value exporter rp_exporter ///
               aff_sales man_aff_sales aff_emp man_aff_emp aff_emp_parent prof_firms  ///
	 (mean) sh_man_emp sh_man_sales sh_dom_man_emp  ///
	 , by(t2 mne_status foreign_manuf) 
	 


 replace sh_dom_man_emp=. if t2=="NON MAN" | mne_status~="MNEFORMAN"
 replace sh_man_emp=. if  t2=="NON MAN" | substr(mne_status,1,3)=="DOM"
  replace sh_man_sales=. if  t2=="NON MAN" | substr(mne_status,1,3)=="DOM"
 		    
*Round for DRB rules:
  do $disc_code/rounding_4sigdig_tf.do sales sales_man emp emp_man emp_prof imp_value rp_imp_value exp_value rp_exp_value 
  do $disc_code/rounding_4sigdig_tf.do  sh_man_emp sh_man_sales sh_dom_man_emp emp_manage
  do $disc_code/rounding_4sigdig_tf.do   aff_sales man_aff_sales  man_aff_emp aff_emp
  do $disc_code/rounding_N_tf.do firms prof_firms importer rp_importer exporter rp_exporter 


replace mne_status = "MNE_FORMAN" if foreign_manuf ==1 & mne_status =="MNE"
     

  egen tot_manage=sum(emp_manage_drb)
  
  gen sh_manage=emp_manage_drb/tot_manage
  gen sh_manage_firm=emp_manage_drb/emp_drb
		    
	    
   
 
*Rounded values to disclose
  export excel t2 mne_status firms_drb sales_man_drb  exporter_drb rp_exporter_drb exp_value_drb rp_exp_value_drb ///
		    aff_emp_drb man_aff_emp_drb  sh_man_emp_drb sh_man_sales_drb sh_dom_man_emp_drb emp_manage_drb ///
		    emp_drb emp_man_drb emp_prof_drb sales_drb /// 
    		    importer_drb rp_importer_drb imp_value_drb rp_imp_value_drb ///
    		    aff_sales_drb man_aff_sales_drb ///
    using "$output/afft_restat_01.xls", sheetreplace sheet(agg_stats)  ///
    firstrow(varlabels)	   
    


***************************************************   
